﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Drawing.Imaging;
using System.Windows.Forms;
using SpectationClient;
using SpectationClient.DataBaseDescription;
using SpectationClient.Stuff;
using SpectationClient.SpectralTools;
using SpectationClient.SQLCommandBuilder;
using SpectationClient.Async;
using GeoAPI.Geometries;
using SpectationClient.DGVExtensions;
using SpectationClient.GUI;
using Npgsql;

namespace SpectationClient.GUI {
    public partial class SubInsertMessprotokollGFZ : Form {
        private struct BGWInfo_INS_GFZ {
            public NpgsqlConnection con;
            //public DB_ID obsarea_id;
            //

               
            public DataBaseInfo DBINFO;
            //Single command statement: use dictionary
            public NpgsqlCommand cmdPhotos_NtoM;
            public NpgsqlCommand cmdPhotos_1toM;
            public NpgsqlCommand cmdWeather;
            public Int32? id_veg_ps;
            public Int32? id_soil;
            public NpgsqlCommand cmdVEG_PS;
            public NpgsqlCommand cmdSoil;
            public NpgsqlCommand cmdVEG_SP;
            public NpgsqlCommand cmdWREF;
            public NpgsqlCommand cmdSPEC;
            public NpgsqlCommand cmdLocation;
            //Look-Up tables to realize 1:M relationships
            public DataTable LUT_SPECTRA_PROCESSES;
            public DataTable LUT_BT_PHOTOS;
        }


        private ViewSpectraForm ViewWRSpectra;
        private ViewSpectraForm ViewFieldSpectra;

        private List<Object> ValueControls = new List<object>();
        private bool suspendValidation = true;
        private bool buildInsertCommand = false;

        private DBManager DBM;
        private ErrorList EL;
        private ESL_Writer ESL_Writer;
        private ASD_Writer ASD_Writer;

        private bool SQL_INIT_CORRECT = true;
        private Dictionary<TableInfo, Object> initObject = new Dictionary<TableInfo, Object>();
        private int sql_init_tables = 0;

        /// <summary>
        /// Background Worker to initialize objects.
        /// </summary>
        private AGetDatabaseObject AGDO;
        private SubReadMessprotokollGFZXLS XLS;
        private DataTable dt_VIEW_OBSERVATION_AREAS;

        private Int32? obsarea_soil = null;
        
        private Regex REGEX_ENDDOTS = new Regex("[.]+$",RegexOptions.Singleline);

        private UC_SetCoordinate uc_COORD;
        
        
        public SubInsertMessprotokollGFZ(ref DBManager dbm) {
            InitializeComponent();

            this.Icon = Resources.Icon;
            this.DBM = dbm;
            
            //this.CMB = new PostGIS_Npgsql_CommandBuilder();
            this.EL = new ErrorList(EP);
            this.EL.ErrorAdded += new EventHandler(EL_ErrorAdded);
            this.EL.ErrorListIsEmpty += new EventHandler(EL_ErrorListIsEmpty);
           
            this.timer.Tick += new EventHandler(timer_Tick);

            this.ESL_Writer = new ESL_Writer();
            this.ASD_Writer = new ASD_Writer();

            this.AGDO = new AGetDatabaseObject();
            this.AGDO.GetDataTableCompleted +=new GetDataTableCompletedEventHandler(AGDO_GetDataTableCompleted);
            this.dt_VIEW_OBSERVATION_AREAS = new DataTable("DT_OBSAREA");


            uc_COORD = new UC_SetCoordinate(ref this.DBM, Properties.Settings.Default.DEF_SRID, ref this.EL);
            ValueControls.Add(uc_COORD);
            this.Loc_Coord.Controls.Add(uc_COORD);
            
            //init Photo DGV columns
            ColumnInfo ci = DBM.getColumnInfo("CORE", "PHOTOS", "purpose");
            FormHelper.initComboBoxItems(Photos_Type, ci.possibleValues);
            //WR_Files.ContextMenuStrip 

            //init WR Columns
            tsmiWRRemoveSpectra.Click +=new EventHandler(MP_WREF_btRemoveSpectra_Click);
            tsmiWRShowSpectra.Click +=new EventHandler(tsmiWRShowSpectra_Click);
            WR_Files.ContextMenuStrip = contextMenuStripWRSpectra;

            //init Speclib Columns
            tsmi_ShowSpectra.Click +=new EventHandler(tsmi_ShowSpectra_Click);
            tsmi_RemoveSpectra.Click +=new EventHandler(MP_OS_btRemoveRows_Click);
            
            SPEC_Field.ContextMenuStrip = contextMenuSpectra;
            SPEC_HyMap.ContextMenuStrip = contextMenuSpectra;
            SPEC_Jump.ContextMenuStrip = contextMenuSpectra;

            initComboBoxes();
            initValueControls();
        }

        void tsmiWRShowSpectra_Click(object sender, EventArgs e) {
            if(this.ViewWRSpectra == null) {
                this.ViewWRSpectra = new ViewSpectraForm();
                this.ViewWRSpectra.linkToSpectrumColumn(WR_Files);
            }
            this.ViewWRSpectra.Show();
        }


        void tsmi_ShowSpectra_Click(object sender, EventArgs e) {
            if(this.ViewFieldSpectra == null) {
                this.ViewFieldSpectra = new ViewSpectraForm();

                this.ViewFieldSpectra.linkToSpectrumColumn(SPEC_Field);
                this.ViewFieldSpectra.linkToSpectrumColumn(SPEC_HyMap);
                this.ViewFieldSpectra.linkToSpectrumColumn(SPEC_Jump);
                if(sender != null) ViewFieldSpectra.Disposed += (sender1, e1) => this.ViewFieldSpectra = null;
            }
            this.ViewFieldSpectra.Show();
        }


        private void initComboBoxes() {

            this.SuspendLayout();
            FormHelper.setLinkAction_SwitchVisualization(gb_COND_WEATHER_cb, gb_COND_WEATHER);
            FormHelper.setLinkAction_SwitchVisualization(gb_COND_FOTOS_cb, gb_COND_FOTOS);
            FormHelper.setLinkAction_SwitchVisualization(gb_COND_ILLUM_cb, gb_COND_ILLUM);

            FormHelper.setLinkAction_SwitchVisualization(gb_COND_SENSOR_cb, gb_COND_SENSOR);
            FormHelper.setLinkAction_SwitchVisualization(gb_COND_SOIL_cb, gb_COND_SOIL);

            FormHelper.setLinkAction_SwitchVisualization(gb_COND_VEGETATION_cb, gb_VEG);
            FormHelper.setLinkAction_SwitchVisualization(gb_VEG_PS_cb, gb_VEG_PS);
            FormHelper.setLinkAction_SwitchVisualization(gb_VEG_SP_cb, gb_VEG_SP);

            FormHelper.setLinkAction_SwitchVisualization(gb_POS_newlocation_rb, gb_POS_newlocation);
            FormHelper.setLinkAction_SwitchVisualization(gb_POS_OBSAREA_rb, gb_POS_OBSAREA);



            this.gb_POS_none_rb.Checked = true;

            this.ResumeLayout();


            //
            this.bt_INSERT.Enabled = false;
            this.timer.Start();
            this.TSL_Action.Text = "Initialisiere Datenbankinformationen";

            //init objects
            DataBaseInfo DBI = this.DBM.getDataBaseInfo();
            this.initObject.Add(DBI["CORE"]["CAMPAIGNS"], Ti_Campaign_cb);
            this.initObject.Add(DBI["CKEYS"]["SOCIABILITY"], SPl_Col_Sociability);
            this.initObject.Add(DBI["CKEYS"]["PLANT_COVERING"], SPl_Col_Cover);
            this.initObject.Add(DBI["C_GFZ"]["VIEW_OBSERVATION_AREAS"], dt_VIEW_OBSERVATION_AREAS);
            this.initObject.Add(DBI["CKEYS"]["GE_KA5_BODENFEUCHTE"], Soil_moisturetext);
            this.initObject.Add(DBI["CKEYS"]["GE_KA5_BEWOELKUNG"], Weather_CloudDensity);
            this.initObject.Add(DBI["CKEYS"]["GE_KA5_WITTERUNG"], Weather_LastPrecip);
            this.initObject.Add(DBI["CKEYS"]["WMO_CLOUDS"], Weather_CloudType);
            this.initObject.Add(DBI["CKEYS"]["WRB_QUALIFIERS"], Soil_WRB_Qualifier);
            this.initObject.Add(DBI["CKEYS"]["WRB_REFERENCESOILGROUPS"], Soil_WRB_SoilGroup);
            this.initObject.Add(DBI["CKEYS"]["WRB_SPECIFIERS"], Soil_WRB_Specifier);
            this.initObject.Add(DBI["CORE"]["OPERATORS"], new Object[] { Ti_OperatorINSERT, Spec_operator, Photo_Operator });
            this.initObject.Add(DBI["CORE"]["WREF_PANELS"], WRef_Panel);
            this.initObject.Add(DBI["CORE"]["CAMERAS"], Photo_Camera);
            this.initObject.Add(DBI["CORE"]["SENSORS"], Sensor_type);



            this.TSL_ProgressBar.Value = 0;
            this.TSL_ProgressBar.Maximum = sql_init_tables += initObject.Count;
            foreach(TableInfo ti in this.initObject.Keys) {
                NpgsqlCommand cmdSelect = CommandBuilder.getSELECT(ti);
                cmdSelect.Connection = this.DBM.Connection;
                AGDO.GetDataTableAsync(cmdSelect, ti);
            }

            //

            ColumnInfo ci = DBM.getColumnInfo("CORE", "SPECTRA", "source");
            FormHelper.initComboBoxItems(Spec_Source, ci.possibleValues, false);
            ColumnInfo ciP = DBM.getColumnInfo("CORE", "PHOTOS", "purpose");

        }
       
        void AGDO_GetDataTableCompleted(object sender, GetDataTableCompletedEventArgs e) {
            TableInfo ti = (TableInfo)e.UserState;

            if(e.Cancelled) {

            } else if(e.Error != null) {
                this.SQL_INIT_CORRECT = false;
                this.RTB.Text += TextHelper.Exception2String(e.Error);
            } else {
                Object[] objects = (this.initObject[ti] is Object[]) ? (Object[])this.initObject[ti] : new Object[]{ this.initObject[ti] };

                foreach(Object o in objects) {

                    ComboBox cb = o as ComboBox;
                    if(cb != null) {
                        FormHelper.initComboBoxItems(cb, e.DataTable, ti);
                    }

                    DataGridViewComboBoxColumn cbc = o as DataGridViewComboBoxColumn;
                    if(cbc != null) {
                        FormHelper.initComboBoxItems(cbc, e.DataTable, ti, true);
                    }

                    DataTable dtToInitialize = o as DataTable;
                    if(dtToInitialize != null) {
                        dtToInitialize.Merge(e.DataTable);
                    }
                }
                this.initObject.Remove(ti);
            }
            this.TSL_ProgressBar.Value = sql_init_tables - initObject.Count;
            if(this.initObject.Count == 0) {
                this.timer.Stop();
                if(this.SQL_INIT_CORRECT) {
                    this.bt_INSERT.Enabled = true;
                    this.TSL_Action.Text = "Initialisierung abgeschlossen";
                    this.RTB.Text = "";
                    VAL_Required(null, null);
                } else {
                    this.TSL_Action.Text = "Initialisierung fehlgeschlagen";
                }
            } 
        }


        void timer_Tick(object sender, EventArgs e) {
            String txt = this.TSL_Action.Text;
            if(txt != null) {
                Match m = this.REGEX_ENDDOTS.Match(txt);
                if(m.Length < 3) {
                    this.TSL_Action.Text += '.';
                } else {
                    this.TSL_Action.Text = txt.Remove(txt.Length - 3);
                }
            }
        }


        void EL_ErrorListIsEmpty(object sender, EventArgs e) {
            if(MP_SPECTRA_DGV.RowCount > 0) this.bt_INSERT.Enabled = true;
        }

        void EL_ErrorAdded(object sender, EventArgs e) {
            this.bt_INSERT.Enabled = false;
        }


        void BGW_INSERT_ProgressChanged(object sender, ProgressChangedEventArgs e) {
            this.TSL_ProgressBar.Value = e.ProgressPercentage;
            if (e.UserState != null && e.UserState.GetType() == typeof(String)) {
                this.TSL_Action.Text = e.UserState.ToString();
            } 
        }



        private void registerVALEvent(Object whatever) {
            if(whatever is GroupBox) {
                foreach(Control c in ((GroupBox)whatever).Controls) {
                    registerVALEvent(c);
                }
            }else if(whatever is Panel){
                foreach(Control c in ((Panel)whatever).Controls) {
                    registerVALEvent(c);
                }
            }else if(whatever is UC_SetCoordinate) {
                ((UC_SetCoordinate)whatever).ValueChanged += new EventHandler(VAL_Required);
                ((UC_SetCoordinate)whatever).SRID_Changed += new EventHandler(VAL_Required);
            } else if(whatever is DataGridView) {
                ((DataGridView)whatever).CellValueChanged +=new DataGridViewCellEventHandler(VAL_Required);
                ((DataGridView)whatever).Rows.CollectionChanged += new CollectionChangeEventHandler(VAL_Required);
            } else if(whatever is Control) {
                Control control = whatever as Control;
                if(control is CheckBox) {
                    ((CheckBox)control).CheckedChanged += new EventHandler(VAL_Required);
                } else if(control is ComboBox) {
                    ((ComboBox)control).SelectedValueChanged += new EventHandler(VAL_Required);
                } else if(control is DateTimePicker) {
                    ((DateTimePicker)control).ValueChanged += new EventHandler(VAL_Required);
                }else if(control is TextBox){
                    ((TextBox)control).TextChanged += new EventHandler(VAL_Required);
                } else {
                    control.TextChanged += new EventHandler(VAL_Required);
                }
            } 
        }

        
        /// <summary>
        /// Assings the different form-objects to the main validation procedure
        /// </summary>
        private void initValueControls(){
            //Validators
            //gb_TITLEDATA
            ValueControls.Add(gb_TITLE);
            ValueControls.Add(gb_SITUATION);
            ValueControls.Add(gb_INSTRUMENTS);
            ValueControls.Add(gb_SPECTRA);
          
            //MP_SPECTRA_DGV.CellValueChanged  += new DataGridViewCellEventHandler(EVENT_VAL_PHOTOPURPOSE);
            //MP_SPECTRA_DGV.CellValueChanged  += new DataGridViewCellEventHandler(MP_OS_DGV_CellValueChanged);
            //MP_SPECTRA_DGV.RowsAdded         += new DataGridViewRowsAddedEventHandler(MP_OS_DGV_RowsAdded);
            //MP_SPECTRA_DGV.RowsRemoved       += new DataGridViewRowsRemovedEventHandler(MP_OS_DGV_RowsRemoved);
            //Photos_DGV.CellValueChanged += new DataGridViewCellEventHandler(EVENT_VAL_PHOTOPURPOSE);

            //init validators
            foreach(Object whatever in ValueControls) {
                registerVALEvent(whatever);
            }
            suspendValidation = false;
        }

       


        private void reactOnLocationChange(object sender, EventArgs e) {
            TextBox tb = sender as TextBox;
            String key = tb.Text;
            AREA_ID_DB_tb.SuspendLayout();
            AREA_ID_GFZ_tb.SuspendLayout();

            if(tb != null) {
                if(tb == AREA_ID_GFZ_tb) {
                    object db_id = DataHelper.getKeyValue(this.dt_VIEW_OBSERVATION_AREAS, 
                                            key, "gfz_area_id", "id");
                    if(db_id != null) {
                        AREA_ID_DB_tb.Text = db_id.ToString();
                        AREA_ID_INFO_tb.Text = TextHelper.combine(DataHelper.getKeyRow(this.dt_VIEW_OBSERVATION_AREAS, key, "gfz_area_id"), " ");
                    } else {
                        AREA_ID_DB_tb.Clear();
                        AREA_ID_INFO_tb.Clear();
                    }

                } else if(tb == AREA_ID_DB_tb) {
                    object gfz_area_id = DataHelper.getKeyValue(this.dt_VIEW_OBSERVATION_AREAS, 
                                            key, "id", "gfz_area_id");
                    if(gfz_area_id != null) {
                        AREA_ID_GFZ_tb.Text = gfz_area_id.ToString();
                        AREA_ID_INFO_tb.Text = TextHelper.combine(DataHelper.getKeyRow(this.dt_VIEW_OBSERVATION_AREAS, key, "id"), " ");
                    } else {
                        AREA_ID_GFZ_tb.Clear();
                        AREA_ID_INFO_tb.Clear();
                    }

                }
            }
            AREA_ID_DB_tb.ResumeLayout();
            AREA_ID_GFZ_tb.ResumeLayout();

        
        }

        /// <summary>
        /// This function starts a validation of the total form
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void VAL_Required(object sender, EventArgs e) {
            if(this.initObject.Count > 0 || this.suspendValidation) {
                bt_INSERT.Enabled = false;
                RTB.Text = "Bitte warten...";
                return;
            } else {
                bool partial = sender != null;
                if(!partial) EL.removeAllErrors();

                if(sender == this.Ti_Institute_tb){
                    bool fromTitle = FormHelper.containsControl(gb_TITLE, sender);
                  
                }
                //gb_TITLE
                if(gb_TITLE_COMMON.Visible && (!partial || FormHelper.containsControl(gb_TITLE_COMMON, sender))) {
                    EL.validation_string(Spec_Source, true);
                    EL.validation_DateBeginEnd(ref Time_TimeStart, ref Time_TimeEnd, false, ErrorList.TimeMode.TimeOfDay);
                }

                if(gb_COND_FOTOS.Visible && (!partial || FormHelper.containsControl(gb_COND_FOTOS , sender))) {
                    //EL.removeError(Photos_DGV);
                     validatePhotoPurpose(Photos_DGV);
                }

                if(gb_POS_newlocation_rb.Checked && (!partial || FormHelper.containsControl(gb_POS_newlocation , sender))){
                    uc_COORD.validate();
                    EL.validation_string(Loc_Locname, true);
                    EL.validation_Number(Loc_Aspect, false);
                    EL.validation_Number(Loc_Slope, false);
                }

                if(gb_POS_OBSAREA_rb.Checked && (!partial || FormHelper.containsControl(gb_POS_OBSAREA , sender))) {
                    String gfz_area_id = AREA_ID_GFZ_tb.Text.Trim();
                    EL.validation_hasValue(AREA_ID_GFZ_tb, false, this.dt_VIEW_OBSERVATION_AREAS, "gfz_area_id");
                    EL.validation_hasValue(AREA_ID_DB_tb, true, this.dt_VIEW_OBSERVATION_AREAS, "id");
                }

                if(gb_COND_WEATHER.Visible &&( !partial || FormHelper.containsControl(gb_COND_WEATHER , sender))) {
                    EL.validation_Number(Weather_A_H, false, 0, 100);
                    EL.validation_Number(Weather_A_Pa, false, 500, 2000);
                    EL.validation_Number(Weather_A_T, false, -60, 60);
                    EL.validation_Number(Weather_W_v, false, 0, 400);
                    EL.validation_Number(Weather_W_dir, false, 0, 360);
                }

                if(gb_COND_SOIL.Visible && (!partial || FormHelper.containsControl(gb_COND_SOIL , sender))) {
                    EL.validation_string(Soil_munsell_hue, 4, false);
                    EL.validation_Int32(Soil_munsell_chroma, false);
                    EL.validation_Int32(Soil_munsell_value, false);
                }

                if(gb_COND_SENSOR.Visible &&( !partial || FormHelper.containsControl(gb_COND_SENSOR , sender))) {
                    //AGL must be greater than AOL!
                    EL.validation_Number(Sens_Height_AGL, ErrorList.Relation.GE, Sens_Height_AOL, false);
                    EL.validation_Number(Sens_Azimuth, false, 0, 360);
                    EL.validation_Number(Sens_Zenith, false, 0, 90);
                }
                if(gb_INSTRUMENTS.Visible  && (!partial || FormHelper.containsControl(gb_INSTRUMENTS , sender))) {
                    
                    EL.validation_Int32(Spec_Optic, false);
                    EL.validation_Number(Spec_Optic, false, 0, 360);
                }
                if(gb_VEG_PS_cb.Checked  && (!partial || FormHelper.containsControl(gb_VEG_PS , sender))) {
                    EL.validation_Number(Veg_S_C_Crypto, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Herbs, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Litter, false, 0, 100);
                    EL.validation_Number(Veg_S_C_oSoil, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Shrubs, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Total, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Trees1, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Trees2, false, 0, 100);
                    EL.validation_Number(Veg_S_H_Herbs, false, 0, 100);
                    EL.validation_Number(Veg_S_H_Shrubs, false, 0, 100);
                    EL.validation_Number(Veg_S_H_Trees1, false, 0, 100);
                    EL.validation_Number(Veg_S_H_Trees2, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Crypto, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Herbs, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Shrubs, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Total, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Trees1, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Trees2, false, 0, 100);
                }

                if(gb_VEG_SP_cb.Checked  && (!partial || FormHelper.containsControl(gb_VEG_SP, sender))) {
                    foreach(DataGridViewRow r in Veg_P_DGV.Rows) {
                        if(!r.IsNewRow) {
                            if(DataHelper.isEmpty(r)) Veg_P_DGV.Rows.Remove(r);
                        }
                    }
                
                }

                if(gb_SPECTRA_WREF.Visible  && (!partial || FormHelper.containsControl( gb_SPECTRA_WREF, sender))) {


                }

                validatePhotoPurpose(MP_SPECTRA_DGV);

                //check if any spectrum was added
                bool hasSpectra = false;
                if(this.MP_SPECTRA_DGV.RowCount > 0) {
                    Int32[] iCols = new Int32[]{
                        SPEC_Field.Index, SPEC_HyMap.Index, SPEC_Jump.Index};
                    
                    foreach(DataGridViewRow row in this.MP_SPECTRA_DGV.Rows) {
                        foreach(Int32 columnIndex in iCols) {
                            if(row.Cells[columnIndex].Value is Spectrum) {
                                hasSpectra = true;
                                break;
                            }
                        }
                        if(hasSpectra) break;
                    }
                }
                if(!hasSpectra) {
                    EL.addError(MP_SPECTRA_DGV, "Kein Spektrum angegeben");
                } else {
                    EL.removeError(MP_SPECTRA_DGV);
                }

               

                EL.checkforDisposedElements();
                if(EL.isEmpty) {
                    bt_INSERT.Enabled = true;

                    //Show commands
                    
                    BGWInfo_INS_GFZ bgi = this.getInsertInfos(null);
                    List<String> cmds = new List<string>();
                    RTB.Clear();
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdLocation));
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdPhotos_1toM));
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdPhotos_NtoM));
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdSoil));
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdVEG_PS));
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdVEG_SP));
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdWeather));
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdSPEC));
                    cmds.Add(CommandBuilder.CommandToString(bgi.cmdWREF));
                    cmds.Add("(Auflistung nicht vollständig, da einige Daten erst zur Laufzeit erzeugt werden)");
                    foreach(String s in cmds) if(s.Length > 0) RTB.Text += s + "\n";
                    
                } else {
                    RTB.Text = "Bitte Angaben überprüfen/vervollständigen.";
                    bt_INSERT.Enabled = false;
                }
            } 
        }

        
        private void Sensor_readSensor_Click(object sender, EventArgs e) {
            OFD.Title = "Einzelnes ASD-Spektrum auswählen...";
            OFD.Multiselect = false;
            if (OFD.ShowDialog() == DialogResult.OK) {
                Byte[] ba = File.ReadAllBytes(OFD.FileName);
                if (ASD_Reader.isValidASDSpectrum(ba)) {
                    int inum = ASD_Reader.getInstrumentNo(ba);
                    int cal_num = ASD_Reader.getCalibrationNo(ba);
                    int fo = ASD_Reader.getForeOptic(ba);
                    if (fo >= 0) Spec_Optic.Text = fo.ToString();

                    TableInfo ti = DBM.getTableInfo("CORE","SENSORS");
                    NpgsqlCommand cmd = CommandBuilder.getSELECT(ti);
                    cmd.Connection = DBM.Connection;
                    NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                    DataTable DT_SENS = new DataTable();
                    da.Fill(DT_SENS);
                    Object id = null;

                    if(DT_SENS.Columns.Contains("cal_no") &&
                       DT_SENS.Columns.Contains("device_id")){


                           id =  (from DataRow r in DT_SENS.Rows
                                    where (int) r["cal_no"] == cal_num &&
                                      (r["device_id"] as String) == (inum.ToString())
                                    select r["id"]).First();
    
                    }
                   
                    if (id != null) {
                        this.Sensor_type.SelectedValue = new Object[]{id};
                    } else {
                        String sens_info = String.Format("\tTyp: {0}\n\tNummer: {1}\n\tKalibrierung: {2}", 
                          ASD_Reader.getInstrumentType(ba).ToString(),
                            inum, cal_num);
                        String info = "Der Sensor:\n" + sens_info + "\nbefindet sich noch nicht in der Datenbank und muss erst eingegeben werden!";
                        MessageBox.Show(info);
                    }
                   
                } else {
                    MessageBox.Show("Bitte ein valides ASD-Spektrum ausgewählen!");
                }
            }
        }

        private void Veg_S_BB_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(Veg_S_BB_tb, this.DBM, "CKEYS", "GE_BIOTOPTYP_BB");
          
        }

        private void Veg_S_EUNIS_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(Veg_S_EUNIS_tb, this.DBM, "CKEYS", "EUNIS_HABITATS");
          
        }

        private void MP_WREF_bt_Click(object sender, EventArgs e) {
            OFD.Multiselect = true;
            OFD.Title = "Bitte Spektraldateien mit Weissreferenzspektren auswählen";

            
            if(OFD.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
              
                SelectSpectraForm SForm = new SelectSpectraForm();
                if(SForm.addSpectra(OFD.FileNames) && SForm.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
                    foreach(Spectrum spec in SForm.getSelectedSpectra()) {
                        this.MP_WREF_DGV.Rows.Add(new Object[]{spec, ""});
                    }
                }            
            }
        }

        private BGWInfo_INS_GFZ getInsertInfos(NpgsqlConnection con) {
            BGWInfo_INS_GFZ bgi = new BGWInfo_INS_GFZ();

            bgi.DBINFO = DBM.getDataBaseInfo();
            if(con != null) bgi.con = con.Clone();
            bgi.cmdPhotos_NtoM = getCMD_PHOTOS_NtoM();
            bgi.cmdWREF = getCMD_WREF();
            bgi.cmdLocation = getCMD_LOCATION();
            getDT_SPECTRA(out bgi.cmdSPEC, out bgi.LUT_SPECTRA_PROCESSES, out bgi.cmdPhotos_1toM, out bgi.LUT_BT_PHOTOS);
            bgi.cmdWeather = getCMD_WEATHER();
            bgi.cmdVEG_PS = getCMD_VegPS();
            bgi.cmdVEG_SP = getCMD_VegSP();
            bgi.cmdSoil = getCMD_SOIL();
            if(bgi.cmdSoil == null && this.obsarea_soil != null) {
                bgi.id_soil = this.obsarea_soil;
            }
            return bgi;
        }

        

        private void bt_INSERT_Click(object sender, EventArgs e) {
            //Get the Value Collections
            this.buildInsertCommand = true;
            this.VAL_Required(null, null);
            if (EL.Count > 0) {
                MessageBox.Show("Bitte die "+EL.Count+" fehlerhaften Felder korrekt ausfüllen", "Fehlerhafte Felder", MessageBoxButtons.OK, MessageBoxIcon.Error);
            } else {
                try {

                    BackgroundWorkerProgressBar BGWP = new BackgroundWorkerProgressBar();
                    BackgroundWorker BGW = BGWP.BackgroundWorker;
                    BGW.RunWorkerCompleted += new RunWorkerCompletedEventHandler(BGW_INSERT_RunWorkerCompleted);
                    BGW.DoWork += new DoWorkEventHandler(BGW_INSERT_DoWork);
          
                    if(BGW.IsBusy) {
                        MessageBox.Show("Bitte warten bis der vorherige Einfügenvorgang abgeschlossen ist.");
                    } else {
                        TSL_ProgressBar.Minimum = 0;
                        TSL_ProgressBar.Maximum = 100;
                        TSL_ProgressBar.Value = 0;
                        TSL_Action.Text = "Füge in Datenbank ein...";
                        BGWInfo_INS_GFZ bgi = this.getInsertInfos(DBM.Connection);
                        BGW.RunWorkerAsync(bgi);
                        this.Enabled = false;
                    }
                } catch (Exception ex) {
                    FormHelper.ShowErrorBox(ex);
                    buildInsertCommand = false;
                } finally {
                    TSL_Action.Text = "";
                }  
            }
            buildInsertCommand = false;
        }


        private NpgsqlCommand getCMD_LOCATION() {
            if(!gb_POS_newlocation.Visible) return null;
            
            TableInfo ti = DBM.getTableInfo("CORE", "LOCATIONS");
            ColumnInfoGeometry gci = (ColumnInfoGeometry)ti["SHAPE"];
            InsertValues rv = new InsertValues(ref ti);
            rv.Add("name", Loc_Locname);
            rv.Add("slope", Loc_Slope);
            rv.Add("aspect", Loc_Aspect);
            if(uc_COORD.hasHeightValue) rv.Add("height", uc_COORD.HeightValue);
            if(uc_COORD.hasXYValues) {
                rv.AddCoordinate("SHAPE", uc_COORD.XValue, uc_COORD.YValue, uc_COORD.SRID);
            }
            if(!rv.IsEmpty) {
                rv.Add("notes", Loc_Notes);
            }
            return rv.getInsertCmd(true);
        }

      
        private NpgsqlCommand getCMD_WEATHER() {
            if(!gb_COND_WEATHER.Visible) return null;
            TableInfo ti = DBM.getTableInfo("C_GFZ", "WEATHER");
            InsertValues rv = new InsertValues(ref ti);
            rv.Add("wind_speed", Weather_W_v);
            rv.Add("wind_azimuth", Weather_W_dir);
            rv.Add("humidity", Weather_A_H);
            rv.Add("air_press", Weather_A_Pa);
            rv.Add("cloudiness", Weather_CloudDensity);
            rv.Add("cloud_type", Weather_CloudType);
            rv.Add("before", Weather_LastPrecip);
            rv.Add("notes", Weather_notes);
            return rv.getInsertCmd(true);
        }

        private NpgsqlCommand getILLUMINANCE() {
            if(!gb_COND_ILLUM.Visible) return null;
            TableInfo ti = DBM.getTableInfo("C_GFZ", "ILLUMINATION");
            InsertValues rv = new InsertValues(ref ti);
            rv.Add("source", Illum_Source);
            rv.Add("zenith", Illum_zenith);
            rv.Add("azimuth", Illum_azimuth);
            rv.Add("illuminance", Illum_illumn);
            rv.Add("illuminance_notes", Illum_notesIllum);
            rv.Add("notes", Illum_notes);
            return rv.getInsertCmd(true);

       
        }

        private NpgsqlCommand getCMD_SOIL() {
            TableInfo ti = DBM.getTableInfo("C_GFZ", "SOILS");
            //DataTable dt = DBM.getEmptyTable("C_GFZ", "SOILS");
            //DataRow nr = dt.NewRow();
            if(!gb_COND_SOIL.Visible) return null;
            InsertValues rv = new InsertValues(ref ti);
            rv.Add("color_notes", Soil_colortext);
            rv.Add("moisture_ka5", Soil_moisturetext);
            rv.Add("munsell_hue", Soil_munsell_hue);
            rv.Add("munsell_chroma", Soil_munsell_chroma);
            rv.Add("munsell_value", Soil_munsell_value);
            rv.Add("wrb_soildescription", Soil_WRB_Description);
            rv.Add("ge_bodenform", Soil_GE_Bodenform);
            rv.Add("ge_bodenbezeichnung", Soil_GE_Bezeichnung);
            if(!rv.IsEmpty) {
                rv.Add("notes", Soil_notes);
                if(Soil_Date.Checked) {
                    rv.Add("date", FormHelper.getMostExactTime(Soil_Date));
                } else {
                    rv.Add("date", FormHelper.getMostExactTime(Time_Date));
                }
            }
            return rv.getInsertCmd(true);

        }



        private void set_SPECTRA_info(ref InsertValues rv, Spectrum SPEC, String description) {
         
            rv.AddID("id_campaign", Ti_Campaign_cb, false);
            rv.AddID("id_operator", Ti_OperatorINSERT, false);
            if(gb_POS_OBSAREA_rb.Checked) {
                String obsAreadID = AREA_ID_DB_tb.Text;
                if(!String.IsNullOrWhiteSpace(obsAreadID)) {
                    var x = (from DataRow r in dt_VIEW_OBSERVATION_AREAS.Rows
                             where (r["id"] as String) == obsAreadID
                             select r["id_location"]).ToList();

                    if(x.Count > 0) {
                        rv.Add("id_location", x.First());
                    }
                }
                
            }
            rv.Add("id_sensor", Sensor_type);
            switch(SPEC.FileType){
                case Spectrum.SpectrumFileType.ASD :
                    rv.Add("file", ASD_Writer.getASDBytes(SPEC));
                    break;
                case Spectrum.SpectrumFileType.ESL :
                    Spectrum[] sl = new Spectrum[]{SPEC};
                    Byte[] mainBlob = ESL_Writer.getMainFile(sl);
                    Byte[] hdrBlob  = ESL_Writer.getHeaderFileBytes(sl);
                    rv.Add("file", mainBlob);
                    rv.Add("file_hdr", hdrBlob);
                    break;
            }
            rv.Add("filename", SPEC.FileName);
            rv.Add("filetype", SPEC.FileTypeString);
            rv.Add("datatype", SPEC.DataTypeString);
            rv.Add("spectype", SPEC.SpecTypeString);
            rv.Add("source", Spec_Source);
            rv.Add("fore_optic", Spec_Optic);
            rv.Add("channels", SPEC.NumberOfBands);
            //rv.Add("ch_width"] =
            rv.Add("wl_min", SPEC.WL_Min);
            rv.Add("wl_max", SPEC.WL_Max);
            //rv.Add("m_count", 
            rv.Add("m_height_agl", Sens_Height_AGL);
            rv.Add("m_height_aol", Sens_Height_AOL);
            rv.Add("m_azimuth", Sens_Azimuth);
            rv.Add("m_zenith", Sens_Zenith);
            rv.Add("date", FormHelper.getMostExactTime(Time_Date));
            rv.Add("time_begin", FormHelper.getMostExactTime(Time_Date, Time_TimeStart));
            rv.Add("time_end", FormHelper.getMostExactTime(Time_Date, Time_TimeEnd));
            rv.Add("notes", description);
        }

        private void getDT_SPECTRA(out NpgsqlCommand cmd_Spectra, out DataTable LUT_Spectra_Processes
                                 , out NpgsqlCommand cmd_Spectra_Photos_1toM, out DataTable LUT_Spectra_Photos_1toM) {

            DataGridViewSpectrumColumn[] specColums = new DataGridViewSpectrumColumn[] { SPEC_Field, SPEC_Jump, SPEC_HyMap};
            String[] processDescription = new String[] {"", "Jump Korrektur","HyMap Resampling"};

            TableInfo tiPHOTOS = DBM.getTableInfo("CORE", "PHOTOS");
            TableInfo tiSPECTRA = DBM.getTableInfo("CORE", "SPECTRA");
            ColumnInfoImageFile ifc = (ColumnInfoImageFile)DBM.getColumnInfo("CORE", "PHOTOS", "file");
            //Insertcommands for spectra and photos
            InsertValueCollection rvcPHOTOS = new InsertValueCollection(ref tiPHOTOS);
            InsertValueCollection rvcSPECTRA = new InsertValueCollection(ref tiSPECTRA);

            //Lookuptables (out variables)
            //LUT idx_spectrum = i'th spectrum id returned from the rvcSPECTRA command 
            //    idx_photo    = i'th photo id returned from the rvcPHOTOS command 
            LUT_Spectra_Photos_1toM = new DataTable();
            LUT_Spectra_Photos_1toM.Columns.Add("id_spectrum", typeof(Int32));
            LUT_Spectra_Photos_1toM.Columns.Add("id_photo", typeof(Int32));

            //LUT idx_child  = i'th spectrum id returned from the rvcSPECTRA command 
            //    idx_parent = i'th photo id returned from the rvcSPECTRA command 
            LUT_Spectra_Processes = new DataTable();
            LUT_Spectra_Processes.Columns.Add("id_child", typeof(Int32));
            LUT_Spectra_Processes.Columns.Add("id_parent", typeof(Int32));
            LUT_Spectra_Processes.Columns.Add("process", typeof(String));
           

            List<int> idx_spectra_for_Photo = new List<int>();
            
            foreach (DataGridViewRow r in MP_SPECTRA_DGV.Rows) {
                bool hasParentSpectrum = false; //becomes true for each non-first column in a iPoint
                bool hasPhoto = false;
                int iPhoto = Photos_File.Index;
                int iPurpose = Photos_Type.Index;
                int iNotes = Photos_Notes.Index;
                
                //Get 1:N Photo information
                if (r.Cells[iPhoto].Value is FileInfo){
                    hasPhoto = true;
                    //ImageFileWrapper IW = (ImageFileWrapper)result.Cells[CN_PHOTOFILE].Value;
                    FileInfo fileInfo = (FileInfo)r.Cells[iPhoto].Value;
                    //DataRow nr = DT_Spectra_Photos.NewRow();
                    InsertValues rvPHOTOS = new InsertValues(ref tiPHOTOS);
                    if(fileInfo.Exists) {
                        if(this.buildInsertCommand) {
                            rvPHOTOS.AddFile(fileInfo, "file", "filename", null, "filetype");

                            byte[] ba = File.ReadAllBytes(fileInfo.FullName);
                            ImageFormat format = DataHelper.getImageFormat(ba);
                            Image preview = DataHelper.makePreview(ba, 300, 300);
                            rvPHOTOS.Add("filepreview", DataHelper.ImageToByteArray(preview, format));


                        } else {
                            rvPHOTOS.Add("file", new Byte[] { });
                            rvPHOTOS.Add("filepreview", new Byte[] { });
                            rvPHOTOS.Add("filename", fileInfo.Name);
                            rvPHOTOS.Add("filetype", "xyz");

                        }
                    }
                    rvPHOTOS.Add("datetime",FormHelper.getMostExactTime(Time_Date, Time_TimeStart, DateTimeKind.Local));
                    rvPHOTOS.Add("purpose", r.Cells[iPurpose]);
                    rvPHOTOS.Add("notes",r.Cells[iNotes]);
                    rvPHOTOS.AddID("id_camera",Photo_Camera, false);
                    rvPHOTOS.AddID("id_operator",Photo_Operator,false);
                    rvcPHOTOS.Add(rvPHOTOS);
                }

                //Get Spectra information
                for(int i=0; i < specColums.Count(); i++) {
                    DataGridViewSpectrumColumn col = specColums[i];
                    String last_process = processDescription[i];


                    if(r.Cells[col.Index].Value is Spectrum) {
                        Spectrum spectrum = r.Cells[col.Index].Value as Spectrum;
                        String notes = r.Cells[SPEC_Notes.Index].Value as String;

                        InsertValues rvSPECTRUM = new InsertValues(ref tiSPECTRA);
                        this.set_SPECTRA_info(ref rvSPECTRUM, spectrum, notes);
                        rvcSPECTRA.Add(rvSPECTRUM);

                        //lookup tableName for photos
                        if(hasPhoto) {
                            LUT_Spectra_Photos_1toM.Rows.Add(new Object[] { rvcSPECTRA.Count - 1, rvcPHOTOS.Count - 1 });
                        }

                        //lookuptable for processing relations
                        if(hasParentSpectrum) {
                            LUT_Spectra_Processes.Rows.Add(new Object[] { 
                                rvcSPECTRA.Count -1 , rvcSPECTRA.Count -2, last_process });
                        }
                        //the next spectrum off this iPoint will have a parent spectrum
                        hasParentSpectrum = true;
                    }
                }
            }
            DataHelper.print(LUT_Spectra_Photos_1toM);
            cmd_Spectra = rvcSPECTRA.getInsertCmd(true);
            cmd_Spectra_Photos_1toM = rvcPHOTOS.getInsertCmd(true);
        }

        private NpgsqlCommand getCMD_VegPS() {
            if(!gb_VEG_PS.Visible) return null;
            TableInfo ti = this.DBM.getTableInfo("C_GFZ","PLANT_SOCIETIES");
            InsertValues rv = new InsertValues(ref ti);
            
            rv.AddID("id_biotope_bb", Veg_S_BB_tb, true);
            rv.AddID("id_eunis_habitat", Veg_S_EUNIS_tb, true);
            rv.AddID("id_bfn_code", Veg_S_BfNCode_tb, true);

            rv.Add("nos_total", Veg_S_NOS_Total);
            rv.Add("nos_trees1", Veg_S_NOS_Trees1);
            rv.Add("nos_trees2", Veg_S_NOS_Trees2);
            rv.Add("nos_shrubs", Veg_S_NOS_Shrubs);
            rv.Add("nos_herbs", Veg_S_NOS_Herbs);
            rv.Add("nos_crypt", Veg_S_NOS_Crypto);

            rv.Add("h_herbs", Veg_S_H_Herbs);
            rv.Add("h_shrubs", Veg_S_H_Shrubs);
            rv.Add("h_trees1", Veg_S_H_Trees1);
            rv.Add("h_trees2", Veg_S_H_Trees2);

            rv.Add("c_total", Veg_S_C_Total);
            rv.Add("c_trees1", Veg_S_C_Trees1);
            rv.Add("c_trees2", Veg_S_C_Trees2);
            rv.Add("c_shrubs", Veg_S_C_Shrubs);
            rv.Add("c_herbs", Veg_S_C_Herbs);
            rv.Add("c_crypto", Veg_S_C_Crypto);
            rv.Add("c_litter", Veg_S_C_Litter);
            rv.Add("c_open_soil", Veg_S_C_oSoil);
            
            if(!rv.IsEmpty) {
                rv.Add("notes", Veg_S_Notes);
                rv.Add("date", FormHelper.getMostExactTime(Time_Date));
            }
            return rv.getInsertCmd(true);
          
        }

        private NpgsqlCommand getCMD_VegSP() {
            if(!gb_VEG_SP.Visible) return null;
            TableInfo ti = DBM.getTableInfo("C_GFZ", "PLANTS");
            InsertValueCollection rvc = new InsertValueCollection(ref ti);
            foreach (DataGridViewRow r in Veg_P_DGV.Rows) {
                if (!r.IsNewRow) {
                    InsertValues rv = new InsertValues(ref ti);
                    DataGridViewCell cell = r.Cells[SPl_Col_Species.Name];
                    rv.AddID("id_species", r.Cells[SPl_Col_Species.Name], true);
                    rv.Add("cover", r.Cells[SPl_Col_Cover.Name]);
                    Object cover =  r.Cells[SPl_Col_Cover.Name].Value;
                    rv.Add("sociability", r.Cells[SPl_Col_Sociability.Name]);
                    rv.Add("phen_day", r.Cells[SPl_Col_PhenDay.Name]);
                    rv.Add("phen_phase", r.Cells[SPl_Col_PhenPhase.Name]);
                    if(!rv.IsEmpty) {
                        rv.Add("notes", r.Cells[SPl_Col_Notes.Name]);
                        rv.Add("date", FormHelper.getMostExactTime(Time_Date));
                        rvc.Add(rv);
                    }
                }
            }
            return rvc.getInsertCmd(true);
        }

        private NpgsqlCommand getCMD_WREF() {
            if(!gb_SPECTRA_WREF.Visible) return null;
            TableInfo ti = DBM.getTableInfo("CORE", "WREF_SPECTRA");
            //DataTable dt = DBM.getEmptyTable("CORE", "WREF_SPECTRA");
            InsertValueCollection rvc = new InsertValueCollection(ref ti);
            //ColumnInfoSpectralFile spi = ti_dgv.Columns["FILE"];
            foreach(DataGridViewRow r in MP_WREF_DGV.Rows) {
                Spectrum S = r.Cells[WR_Files.Index].Value as Spectrum;
                if(S == null) {
                    //String temp = "";
                    continue;
                }
                String notes = r.Cells[WR_Description.Index].Value as String;
                InsertValues rv = new InsertValues(ref ti);
                rv.Add("id_panel", WRef_Panel);
                rv.Add("id_sensor", Sensor_type);
                if(this.buildInsertCommand) {
                    switch(S.FileType) {
                        case Spectrum.SpectrumFileType.ASD:
                            rv.Add("file", ASD_Writer.getASDBytes(S));
                            break;
                        case Spectrum.SpectrumFileType.ESL:
                            Spectrum[] sl = new Spectrum[] { S };
                            Byte[] mainBlob = ESL_Writer.getMainFile(sl);
                            string headerString = ESL_Writer.getHeaderFileText(sl);
                            rv.Add("file", mainBlob);
                            rv.Add("file_hdr", headerString);
                            break;
                    }
                } else {
                    rv.Add("file",  new Byte[] { });
                    rv.Add("file_hdr", new Byte[] { });
                }
                
                rv.Add("filename", S.FileName);
                rv.Add("filetype", S.FileTypeString);
                rv.Add("spectype", S.SpecTypeString);
                rv.Add("datatype", S.DataType.Name);
                if(!rv.IsEmpty) {
                    rv.Add("notes", r.Cells[WR_Description.Index]);
                    rv.Add("datetime", FormHelper.getMostExactTime(Time_Date, Time_TimeStart, DateTimeKind.Local));
                    rvc.Add(rv);
                }
            }
            return rvc.getInsertCmd(true);
        }

        private NpgsqlCommand getCMD_PHOTOS_NtoM() {
            if(!gb_COND_FOTOS_cb.Checked) return null;
            TableInfo ti = DBM.getTableInfo("CORE","PHOTOS");
            InsertValueCollection rvc = new InsertValueCollection(ref ti);
            ColumnInfoImageFile ifc = (ColumnInfoImageFile)ti["file"];
            //DataTable dt = DBM.getEmptyTable("CORE","PHOTOS");

            int iPhoto = Photos_File.Index;
            int iPurpose = Photos_Type.Index;
            int iNotes = Photos_Notes.Index;

            foreach (DataGridViewRow r in Photos_DGV.Rows) {
                if (!r.IsNewRow && r.Cells[iPhoto].Value != null) {
                    InsertValues rv = new InsertValues(ref ti);
                    DGVExtensions.DataGridViewPhotoCell cell = (DGVExtensions.DataGridViewPhotoCell)r.Cells[iPhoto];
                    if(cell.Value is FileInfo) {
                        FileInfo fileInfo = cell.Value as FileInfo;
                        if(this.buildInsertCommand) {
                            byte[] ba = File.ReadAllBytes(fileInfo.FullName);
                            ImageFormat format = DataHelper.getImageFormat(ba);
                            Image preview = DataHelper.makePreview(ba, 300, 300);
                            rv.Add("filepreview", DataHelper.ImageToByteArray(preview, format));
                            rv.AddFile(fileInfo, "file", "filename", null, "filetype");
                        } else {
                            rv.Add("filepreview", new Byte[]{});
                            //rv.AddFile(new Byte[] { }, "file", "filename", null, "filetype");
                        }
                        rv.Add("purpose", r.Cells[iPurpose]);
                        rv.Add("id_camera", Photo_Camera);
                        rv.Add("id_operator", Photo_Operator);
                    
                    }
                    
                  
                    if(!rv.IsEmpty) {
                        rv.Add("notes", r.Cells[iNotes]);
                        rv.Add("datetime", FormHelper.getMostExactTime(Time_Date, Time_TimeStart, DateTimeKind.Local));
                    }
                    rvc.Add(rv);
                
                }
            }
            return rvc.getInsertCmd(true);
        }
        
        
        private void Veg_P_DGV_CellContentClick(object sender, DataGridViewCellEventArgs e) {
            String[]display = new String[]{"gatt", "art", "pk_artcode"};
            if(e.ColumnIndex == 0) { 
            
                this.suspendValidation = true;
                DataGridView dgv = ((DataGridView) sender);

                int iRow = (e.RowIndex == -1) ? dgv.Rows.Add(1) :e.RowIndex;
                DataGridViewCell senderCell = dgv.Rows[iRow].Cells[e.ColumnIndex];
                DataGridViewColumn targetColumn = dgv.Columns["SPl_Col_Species"];
                FormHelper.setInfoFromSelectionTable(senderCell, targetColumn, display.ToList(),
                    DBM, "CKEYS", "GE_ARTEN_BUNDESLISTE", true, "Artenbundesliste", "Art auswählen");
                this.suspendValidation = false;
            }
        }

        private void MP_OS_Read_Click(object sender, EventArgs e) {
            OFD.Multiselect = true;

            int iRow = 0;
            int iColumn = 0;

            int nSelected = MP_SPECTRA_DGV.GetCellCount(DataGridViewElementStates.Selected);
            if( nSelected > 0){
                for(int i = nSelected - 1; i >= 0; i--){
                    if(MP_SPECTRA_DGV.SelectedCells[i] is DataGridViewSpectrumCell){
                        iRow = MP_SPECTRA_DGV.SelectedCells[i].RowIndex;
                        iColumn = MP_SPECTRA_DGV.SelectedCells[i].ColumnIndex;
                        break;
                    }
                }   
            }

            OFD.Title = "Bitte Spektraldatei(en) auswählen";
            OFD.Multiselect = true;

            List<Spectrum> spectraToInsert = null;
            
            if(OFD.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
                SelectSpectraForm SForm = new SelectSpectraForm();
                if(SForm.addSpectra(OFD.FileNames) && SForm.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
                    this.suspendValidation = true;
                    spectraToInsert = SForm.getSelectedSpectra();
                    for(int i =0; i < spectraToInsert.Count; i++){

                        Spectrum spec = spectraToInsert[i];
                        if(MP_SPECTRA_DGV.RowCount == iRow) {
                            MP_SPECTRA_DGV.Rows.Add();
                        }
                        this.MP_SPECTRA_DGV[iColumn, iRow].Value = spec;
                        iRow++;
                    }
                    this.suspendValidation = false;
                    this.VAL_Required(MP_SPECTRA_DGV, null);
                }
            }
        }

        private void Photos_btAddPhotos_Click(object sender, EventArgs e) {
            List<FileInfo> files = FormHelper.getPhotoFileInfos(true, "Bitte Fotos auswählen");
            
            int iPhoto = Photos_File.Index;

            List<FileInfo> existing = (from DataGridViewRow row in Photos_DGV.Rows
                                       where row.Cells[iPhoto].Value is FileInfo
                                       select (FileInfo) row.Cells[iPhoto].Value).ToList();

            files = files.Except(existing).ToList();
                               
            foreach(FileInfo newFile in files){
                int iNew = Photos_DGV.Rows.Add();
                Photos_DGV.Rows[iNew].Cells[iPhoto].Value = newFile;
                Photos_DGV.Rows[iNew].Cells[iPhoto].ToolTipText = newFile.FullName;
                if (Photos_DGV.RowCount == 1) Photos_DGV_SelectionChanged(null, null);
            }

             
        }

        protected List<DataGridViewRow> getSelectedRows2(DataGridView dgv) {
            List<int> rowids = new List<int>();
            List<DataGridViewRow> row_coll = new List<DataGridViewRow>();
            foreach (DataGridViewCell cell in dgv.SelectedCells) {
                rowids.Add(cell.RowIndex);
            }
            var rows = (from r in rowids select r).Distinct();
            rowids = rows.ToList<int>();
            foreach (int r_idx in rowids) {
                row_coll.Add(dgv.Rows[r_idx]);
            }
            return row_coll;
        }


        private void Photos_DGV_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e) {
            EL.checkforDisposedElements();
            if (Photos_DGV.RowCount == 0) Photos_PreviewBox.Image = null;
        }

        private void Photos_DGV_SelectionChanged(object sender, EventArgs e) {
            List<DataGridViewRow> dr = getSelectedRows2(Photos_DGV);
            int iPhoto = Photos_File.Index;
            foreach(DataGridViewRow row in dr){
                DGVExtensions.DataGridViewPhotoCell CELL = row.Cells[iPhoto] as DGVExtensions.DataGridViewPhotoCell;
                if(CELL != null && CELL.Value != null){
                    Image img = CELL.getImage();
                    Photos_PreviewBox.Image = img;
                }
            
            }
        }

        private void validatePhotoPurpose(Object sender, EventArgs arg) {
            this.validatePhotoPurpose((DataGridView)sender);
        }
        private void validatePhotoPurpose(DataGridView dgv) {
            String name = dgv.Name;
            int iPhoto = Photos_File.Index;
            int iPurpose = Photos_Type.Index;


            foreach(DataGridViewRow row in dgv.Rows) {
                if (row.Cells[iPhoto].Value != null && 
                    (row.Cells[iPurpose].Value == null || row.Cells[iPurpose].Value == DBNull.Value)){
                    EL.addError(row.Cells[iPurpose], "Bitte Wert auswählen");
                }else{
                    EL.removeError(row.Cells[iPurpose]);
                }
            }
            
        }
     
        private void xlsEinlesenToolStripMenuItem_Click(object sender, EventArgs e){
            if(this.XLS != null) {
                XLS.Focus();
                //XLS.TopLevel = true;
            } else {
                XLS = new SubReadMessprotokollGFZXLS();
                XLS.Ready2ReadValues += new EventHandler(XLS_Ready2ReadValues);
                XLS.Disposed += new EventHandler(XLS_Disposed);
                XLS.Show();
            }

        }

        void XLS_Disposed(object sender, EventArgs e) {
            XLS = null;
        }

        /// <summary>
        /// This function accesses the XLS R1 and reads values from the recent shown DataGridView
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void XLS_Ready2ReadValues(object sender, EventArgs e) {
            SubReadMessprotokollGFZXLS XLSR = (SubReadMessprotokollGFZXLS)sender;
            //read values 
            String temp_s;
            Object temp_obj;
           
            //Institut B2
            XLSR.setValue(Ti_Institute_tb, "B", 2, "Institut");

            //Bearbeiter--> 1. = ASD, 2. = Foto / Protokoll
            Object Operators = XLSR.getValue("B", 3, typeof(string), "Bearbeiter");
            if(Operators != null) {
                Char[] sep = new char[] { ',', ';', ' ' };
                String[] names = Operators.ToString().Split(sep, StringSplitOptions.RemoveEmptyEntries);
                if(names.Length > 0) {
                    if(!selectInCombobox(names[0], this.Spec_operator, sep, false))
                        XLSR.setMessage("B",3, "Bearbeiter", "Bediener Spektrometer ist nicht in der Datenbank vorhanden");
                }
                if(names.Length > 1) {
                    if(!selectInCombobox(names[1], this.Photo_Operator, sep, false))
                        XLSR.setMessage("B",3, "Bearbeiter", "Bediener Fotokamera/Protokoll ist nicht in der Datenbank vorhanden");
                } 
            }
            
            //Datum
            XLSR.setValue(Time_Date, "B", 4, typeof(DateTime), "Datum");

            //Time
            XLSR.setValue(Time_TimeStart, "C", 5, typeof(DateTime), "Startzeit");
            XLSR.setValue(Time_TimeEnd, "C", 6, typeof(DateTime), "Endzeit");


            //Witterung C10 Bsp: WT3/H1
            temp_obj = XLSR.getValue("C", 10, "Witterung/Wolkendichte");
            if(temp_obj != null) {
               temp_s = temp_obj.ToString();
               gb_COND_WEATHER_cb.Checked = true;
               Char[] sep = new char[] { '/', '|', '\\', ';', ' ' };
               if(!selectInCombobox(temp_s, this.Weather_LastPrecip, sep, false))
                   XLSR.setMessage("C",10, "Witterung","keine Übereinstimmung mit Datenbankeintrag gefunden");
               if(!selectInCombobox(temp_s, this.Weather_CloudDensity, sep, false))
                   XLSR.setMessage("C",10, "Wolkendichte","keine Übereinstimmung mit Datenbankeintrag gefunden");
            }
            
            //GFZ Observation Area
            uc_COORD.clear();
            AREA_ID_GFZ_tb.Clear();
            if(XLSR.setValue(AREA_ID_GFZ_tb, "E", 3, "Standpunkt (GFZ Observationsfläche", false)) {
                gb_POS_OBSAREA_rb.Checked = true;
                
            } else{
                object xvalue = XLSR.getValue("E", 4, typeof(float), "Ostwert/Längengrad", false);
                object yvalue = XLSR.getValue("E", 5, typeof(float), "Hochwert/Breitengrad", false);
                if(xvalue != null) uc_COORD.XText = xvalue.ToString();
                if(yvalue != null) uc_COORD.YText = yvalue.ToString();
                if(xvalue != null || yvalue != null) {
                    gb_POS_newlocation_rb.Checked = true;
                    
                }
            }

            //Wolkengattung D10 Bsp:
            if(XLSR.setValue(this.Weather_CloudType, "C", 10, "Wolkengattung")){
                gb_COND_WEATHER_cb.Checked = true;
            } 
            
            //Bodenfarbe A12 Bsp.: 
            if(XLSR.setValue(Soil_colortext, "A", 12, "Bodenfarbe")) {
                gb_COND_SOIL_cb.Checked = true;
            }

            //Bodenfeuchte B12 Bsp: F3 (KA5)
            if(XLSR.setValue(Soil_moisturetext, "B", 12, "Bodenfeuchte")) {
                gb_COND_SOIL_cb.Checked = true;
            }

            //FotoNr: D12 Bsp: "Fla_wolken" --> Pfad???
            //ASD Höhe über Grund G10
            if(XLSR.setValue(Sens_Height_AGL, "G", 10, typeof(Single), "Sensorhöhe über Grund")) {
                gb_COND_SENSOR_cb.Checked = true;  
            } 

            //ASD Höhe über Objekt G11
            if(XLSR.setValue(Sens_Height_AOL, "G", 11, typeof(Single), "Sensorhöhe über Objekt")) {
                gb_COND_SENSOR_cb.Checked = true;  
            } 

            //Beschreibung Vegetation A15
            if(XLSR.setValue(Veg_S_Notes, "A", 15, "Beschreibung Vegetation")) {
                gb_VEG_PS_cb.Checked = true;  
            } 
           
            //Wuchshöhe Krautschicht F15 Bsp: 0.5m
            if(XLSR.setValue(Veg_S_H_Herbs, "F", 15, typeof(float), "Wuchshöhe Krautschicht")) {
                gb_VEG_PS_cb.Checked = true;  
            } 

            //Wuchshöhe Strauchschicht F16
            if(XLSR.setValue(Veg_S_H_Shrubs, "F", 16, typeof(float), "Wuchshöhe Strauchschicht")) {
                gb_VEG_PS_cb.Checked = true;
            } 

            //ASD Nr. A19 Bsp 3 
            XLSR.setValue(Sensor_type, "A", 19, typeof(string), "Seriennummer ASD");

            //ForeOptik in Grad B19
            XLSR.setValue(Spec_Optic, "B", 19, typeof(float), "ForeOptic (Öffnungswinkel)");

            //Spektralon C19 Bsp "klein"
            XLSR.setValue(WRef_Panel, "C", 19, "Spektralon");

            //ASD/Spektralon Bemerkungen E9/D19?
            XLSR.setValue(Ti_notes, "E", 9, "Bemerkungen ASD/Spektralon");
            
            XLSR.setMessage("Achtung: die Information ab Zeile 21 müssen (leider noch) per Hand eingeben werden.", SubReadMessprotokollGFZXLS.MessageType.info); 
            //#Messungen, erste Zeile bis EOF
            //Datei Basisname B21 beispiel "doeb_xyz"
            //Mess Nummer A24 -> EOF 
            //Flächen ID B24 -> EOF
            //Fehlmeldungen X C24 -> EOF
            //Kalibrierung (text) D24
            //Uhrzeit Begin/Ende E24
            //Foto nummer + Bemerkung F24
            //this.VAL_Required(null, null);
            XLSR.BringToFront();

        }
      
        private bool selectInCombobox(String s, ComboBox cb, bool case_sensitive){
            Char[] sep = new char[]{',',';',' '};
            return selectInCombobox(s, cb, sep, case_sensitive);
        }

        private bool selectInCombobox(String s, ComboBox cb, Char[] sep, bool case_sensitive){
            bool ret = false;
            s = s.Trim();
            if(s.Length == 0) return false;

            String[] s_part = s.Split(sep);
            //check the value members
            if(cb.DataSource != null && cb.DataSource.GetType() == typeof(DataTable)) {
                DataTable dt = (DataTable)cb.DataSource;
                int[] matches = new int[dt.Rows.Count];
                String[] val_disp, val_val = null;
                int n_match, n_max_match, i_max_match;
                n_match = n_max_match = i_max_match = 0;
                
                for(int i = 0; i < matches.Length; i++) {
                    val_disp = dt.Rows[i][cb.DisplayMember].ToString().Trim().Split(sep);
                    val_val = dt.Rows[i][cb.ValueMember].ToString().Trim().Split(sep);
                    n_match = Math.Max(this.numberStringMatches(s_part, val_disp, case_sensitive),
                                       this.numberStringMatches(s_part, val_val, case_sensitive));
                    if(n_match > n_max_match) {
                        i_max_match = i;
                        n_max_match = n_match;
                    }
                }
                //use the item with most matches, if there was attributes least one match!
                if(n_max_match > 0) {
                    ret = true;
                    cb.SelectedIndex = i_max_match;
                }
            }

            return ret;
        }


        private int numberStringMatches(String[] a, String[] b, bool case_sensitive) {
            int n = 0;
            for(int i = 0; i < a.Length; i++) {
                for(int j = 0; j < b.Length; j++) {
                    if(case_sensitive) {
                        if(String.Equals(a[i], b[j],  StringComparison.Ordinal)) n++;
                    } else {
                        if(String.Equals(a[i], b[j], StringComparison.OrdinalIgnoreCase)) n++;
                    }
                }
            }
            return n;
        }

        private void AREA_ID_bt_Click(object sender, EventArgs e) {
            try {
                SubShowSelectionTable SS = new SubShowSelectionTable(ref this.DBM, "C_GFZ", "VIEW_OBSERVATION_AREAS", false, false,
                    "Bitte vorhandene Beobachtungsfläche auswählen", "Fläche übernehmen");
                if(SS.ShowDialog() == DialogResult.OK) {
                    AREA_ID_DB_tb.Text = String.Format("{0}", SS.getSelectedColumnValues("id")[0]);
                    AREA_ID_GFZ_tb.Text = String.Format("{0}", SS.getSelectedColumnValues("gfz_area_id")[0]);
                    AREA_ID_INFO_tb.Text = TextHelper.combine<Object>(SS.getSelectedValues()[0], " ");

                    
                }
            } catch(Exception ex) {
                FormHelper.ShowErrorBox(ex);
            }
        }


        private void Veg_S_BfNCode_bt_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(Veg_S_BfNCode_tb, DBM, "CKEYS", "BFN_CODE",
                  "Bitte BfN Eintrag auswählen", "Eintrag übernehmen");
        }

        private void eingabenZurücksetzenToolStripMenuItem_Click(object sender, EventArgs e) {
            FormHelper.ResetControls(this);
            EL.checkforDisposedElements();
        }

        private void gb_VEG_SP_btAdd_Click(object sender, EventArgs e) {
            DataGridViewCellEventArgs args = new DataGridViewCellEventArgs(0, Veg_P_DGV.NewRowIndex);
            Veg_P_DGV_CellContentClick(Veg_P_DGV, args);
        }

        private void gb_VEG_SP_btRemove_Click(object sender, EventArgs e) {
            FormHelper.removeSelectedRows(Veg_P_DGV);
        }

        private void Photos_btRemovePhotos_Click(object sender, EventArgs e) {
            FormHelper.removeSelectedRows(Photos_DGV);
        }

        private void MP_OS_btRemoveRows_Click(object sender, EventArgs e) {
            FormHelper.removeSelectedCells(MP_SPECTRA_DGV);
            FormHelper.removeEmptyRows(MP_SPECTRA_DGV);
        }

        private void MP_WREF_DGV_SelectionChanged(object sender, EventArgs e) {
            MP_WREF_btRemoveSpectra.Enabled = MP_WREF_DGV.SelectedRows.Count > 0;
        }

        private void MP_WREF_btRemoveSpectra_Click(object sender, EventArgs e) {
            FormHelper.removeSelectedRows(MP_WREF_DGV);
        }

        private void MP_SPECTRA_DGV_SelectionChanged(object sender, EventArgs e) {
            gb_SPECTRA_OBJ_btRemove.Enabled = MP_SPECTRA_DGV.SelectedCells.Count > 0 ||
                                              MP_SPECTRA_DGV.SelectedRows.Count > 0;
        }

        private void Veg_P_DGV_DataError(object sender, DataGridViewDataErrorEventArgs e) {
            FormHelper.ShowErrorBox(e.Exception);
        }

        private void Veg_S_BB_tb_TextChanged(object sender, EventArgs e) {
            Veg_S_BB_btRemove.Enabled = !String.IsNullOrWhiteSpace(Veg_S_BB_tb.Text);
        }

        private void Veg_S_EUNIS_tb_TextChanged(object sender, EventArgs e) {
            Veg_S_EUNIS_btRemove.Enabled = !String.IsNullOrWhiteSpace(Veg_S_EUNIS_tb.Text);
        }

        private void Veg_S_BfNCode_tb_TextChanged(object sender, EventArgs e) {
            Veg_S_BfNCode_btRemove.Enabled = !String.IsNullOrWhiteSpace(Veg_S_BfNCode_tb.Text);
        }

        private void Veg_S_BB_btRemove_Click(object sender, EventArgs e) {
            Veg_S_BB_tb.Tag = null;
            Veg_S_BB_tb.Text = null;
        }

        private void Veg_S_EUNIS_btRemove_Click(object sender, EventArgs e) {
            Veg_S_EUNIS_tb.Tag = null;
            Veg_S_EUNIS_tb.Text = null;
        }

        private void Veg_S_BfNCode_btRemove_Click(object sender, EventArgs e) {
            Veg_S_BfNCode_tb.Tag = null;
            Veg_S_BfNCode_tb.Text = null;
        }

        

    }
}
